package com.k2data.Utils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

public class MysqlService {
	private static final Log LOG = LogFactory.getLog(MysqlService.class);

	/**
	 * 
	 * @param host
	 * @param port
	 * @param db
	 * @param user
	 * @param password
	 * @return
	 */
	static public Connection getConnection(String host, long port, String db, String user, String password) {

		if (host == null || host.trim().isEmpty()) {
			LOG.error("must set mysql host");
			return null;
		}

		try {
			Class.forName("com.mysql.jdbc.Driver");
			StringBuffer sb = new StringBuffer();
			sb.append("jdbc:mysql://").append(host).append(":").append(port).append("/");
			sb.append(db).append("?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true");
			String connect_url = sb.toString();
			Class.forName("com.mysql.jdbc.Driver");
			return DriverManager.getConnection(connect_url, user, password);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return null;
	}

	static public Connection getConnection(String dbUrl, String user, String password) {

		if (dbUrl == null || dbUrl.trim().isEmpty()) {
			LOG.error("must set mysql url");
			return null;
		}

		try {
			Class.forName("com.mysql.jdbc.Driver");
			return DriverManager.getConnection(dbUrl + "?rewriteBatchedStatements=true&useServerPrepStmts=true", user, password);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return null;
	}

	/**
	 * 
	 * @param connection
	 * @param sql
	 * @return
	 */
	public static ResultSet executeQuery(Connection connection, String sql) {
		PreparedStatement preparedStatement = null;
		try {
			preparedStatement = connection.prepareStatement(sql);
			return preparedStatement.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * chech if exist the table tabName
	 * 
	 * @param stmt
	 *            Statement
	 * @param tabName
	 *            table name to check
	 * @return true: is exist else false
	 * @throws SQLException
	 */
	static private boolean isExistTable(Statement stmt, String tabName) throws SQLException {
		String sql = "show tables";
		ResultSet res = stmt.executeQuery(sql);
		while (res.next()) {
			String name = res.getString(1);
			if (name.equalsIgnoreCase(tabName)) {
				return true;
			}
		}
		return false;
	}

}
